/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package it.barattando.bean;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

/**
 *
 * @author carlo.bison
 */
public class DbController {
    
    private Connection connection;
    
    public DbController(String confFileName) throws NamingException, SQLException {
        InitialContext context = new InitialContext();
        DataSource ds = (DataSource) context.lookup("java:comp/env/" + confFileName);
        this.connection = ds.getConnection();
    }
    
    public DbController() throws ClassNotFoundException, SQLException{
        Class.forName("com.mysql.jdbc.Driver");
        String dbUrl = "jdbc:mysql://localhost/barattando2003";
        connection = DriverManager.getConnection(dbUrl, "root", "admin");
    }
    
    public void addUser(String userName,String pwd,String firstName,String lastName,String region,String province,String town,String birthday,String address,String cf) throws SQLException{
        
        int result;
        
        CallableStatement storedProcedure;
        storedProcedure = this.connection.prepareCall("{call INS_NUOVO_UTENTE(?,?,?,?,?,?,?,?,?,?)}");
        storedProcedure.setString(1, userName);
        storedProcedure.setString(2, pwd);
        storedProcedure.setString(3, firstName);
        storedProcedure.setString(4, lastName);
        storedProcedure.setString(5, region);
        storedProcedure.setString(6, province);
        storedProcedure.setString(7, town);
        storedProcedure.setString(8, birthday);
        storedProcedure.setString(9, address);
        storedProcedure.setString(10, cf);
        
        storedProcedure.execute();
        
        storedProcedure.close();
        
        storedProcedure = this.connection.prepareCall("{call FT_CREA_TBAN_LOGIN(?)}");
        storedProcedure.registerOutParameter(1, java.sql.Types.INTEGER);
        
        storedProcedure.execute();
        result=storedProcedure.getInt(1);
        System.out.println("result: "+result);
        storedProcedure.close();
        
    }
    
    public boolean login(String userName,String pwd) throws SQLException{
        
        int result;
        CallableStatement storedProcedure;
        storedProcedure = this.connection.prepareCall("{call LOGIN(?,?,?)}");
        storedProcedure.setString(1, userName);
        storedProcedure.setString(2, pwd);
        storedProcedure.registerOutParameter(3, java.sql.Types.INTEGER);
        
        storedProcedure.execute();
        result=storedProcedure.getInt(3);
        System.out.println("result: "+result);
        storedProcedure.close();
        
        if(result==1)
            return true;
        else
            return false;
        
    }
    
    public ArrayList<SellingObject> getSellingObjects(String userName) throws SQLException{
        ArrayList<SellingObject> ret = new ArrayList<SellingObject>();
        int result;
        CallableStatement storedProcedure;
        storedProcedure = this.connection.prepareCall("{call ESTR_OGGETTI_ATTIVI_UTENTE(?,?)}");
        storedProcedure.setString(1, userName);
        storedProcedure.registerOutParameter(2, java.sql.Types.INTEGER);
        
        storedProcedure.execute();
        
        System.out.println("eseguita stored procedure ");
        result = storedProcedure.getInt(2);
        System.out.println("risultato: "+result);
        
        ResultSet res = storedProcedure.getResultSet();
        SellingObject newObject;
        while(res.next()){
            newObject=new SellingObject(res.getString("OGGETTO"),res.getString("OGGETTO_DESCR"),res.getString("OGGETTO_CATEGORA"),res.getInt("OGGETTO_CREDITI_VAL"),userName);
            ret.add(newObject);
        }
        
        storedProcedure.close();
        
        return ret;
        
    }
    
    public void addSellingObject(SellingObject newSellingObject) throws SQLException{
        
        CallableStatement storedProcedure;
        storedProcedure = this.connection.prepareCall("{call INS_NUOVO_OGGETTO(?,?,?,?,?)}");
        storedProcedure.setString(1, newSellingObject.getTitle());
        storedProcedure.setString(2, newSellingObject.getDescription());
        storedProcedure.setString(3, newSellingObject.getCategory());
        storedProcedure.setInt(4, newSellingObject.getValue());
        storedProcedure.setString(5, newSellingObject.getOwner());
        
        storedProcedure.execute();
        
        storedProcedure.close();
        
    }
    
    public ArrayList<String> getRegions() throws SQLException{
        
        int result;
        ArrayList<String> ret = new ArrayList<String>();
        CallableStatement storedProcedure;
        storedProcedure = this.connection.prepareCall("{call ESTR_REGIONI(?)}");
                
        storedProcedure.registerOutParameter(1, java.sql.Types.INTEGER);
        
        storedProcedure.execute();
        
        System.out.println("eseguita stored procedure ");
        result = storedProcedure.getInt(1);
        System.out.println("risultato: "+result);
        
        System.out.println("eseguita stored procedure ");
        
        ResultSet res = storedProcedure.getResultSet();
        SellingObject newObject;
        while(res.next()){
            ret.add(res.getString("REGIONE"));
        }
        
        storedProcedure.close();
        
        return ret;
    }
    
    public ArrayList<String> getProvinces(String region) throws SQLException{
        
        int result;
        ArrayList<String> ret = new ArrayList<String>();
        CallableStatement storedProcedure;
        storedProcedure = this.connection.prepareCall("{call ESTR_PV(?,?)}");
        storedProcedure.setString(1, region);
        storedProcedure.registerOutParameter(2, java.sql.Types.INTEGER);
        
        storedProcedure.execute();
        
        System.out.println("eseguita stored procedure ");
        result = storedProcedure.getInt(2);
        System.out.println("risultato: "+result);
        
        System.out.println("eseguita stored procedure ");
        
        ResultSet res = storedProcedure.getResultSet();
        SellingObject newObject;
        while(res.next()){
            ret.add(res.getString("PROVINCIA"));
        }
        
        storedProcedure.close();
        
        
        return ret;
    }
    
    public static void main(String[] argv){
        try {
            DbController dBcontroller = new DbController();
            //dBcontroller.addUser("user1", "pwd1", "name1", "surname1", "Lombardia", "VA", "Gallarate", "1984-08-11 00:00:00", "via casati", "blabla84a12b345c");
            boolean logged=dBcontroller.login("user1", "pwd1");
            if(logged)
                System.out.println("loggato");
            else
                System.out.println("non loggato");
            
            ArrayList<SellingObject> myObjects;
            myObjects = dBcontroller.getSellingObjects("TACKSUN");
            
            for(SellingObject obj:myObjects){
                System.out.println("title: "+obj.getTitle()+" value: "+obj.getValue());
            }
            
            SellingObject newObject=new SellingObject("clio","molto usata","automobile",2,"TACKSUN");
            //dBcontroller.addSellingObject(newObject);
            
            System.out.println("OGGETTO AGGIUNTO");
            
            myObjects = dBcontroller.getSellingObjects("TACKSUN");
            
            for(SellingObject obj:myObjects){
                System.out.println("title: "+obj.getTitle()+" value: "+obj.getValue());
            }
            
            ArrayList<String> regions;
            regions=dBcontroller.getRegions();
            
            for(String region:regions){
                System.out.println("regione: "+region);
            }
            
            ArrayList<String> provinces;
            provinces = dBcontroller.getProvinces("Lombardia");
            
            for(String province:provinces){
                System.out.println("provincia: "+province);
            }
            
            
        } catch (ClassNotFoundException ex) {
            System.out.println(ex.getLocalizedMessage());
        } catch (SQLException ex) {
            System.out.println("SQL EXCEPTION: "+ex.getLocalizedMessage());
        }
    }
    
}
